package com.jandar.service;

import com.alibaba.fastjson.JSONObject;
import com.jandar.config.AppConfig;
import com.jandar.entity.BillExcelSource;
import com.jandar.entity.LacCiAddressMapping;
import com.jandar.util.DynamicQueryUtil;
import com.querydsl.core.Tuple;
import com.querydsl.jpa.impl.JPAQueryFactory;
import lombok.Data;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.*;
import java.util.stream.Collectors;

import static com.jandar.constant.QTableConstant.*;


@Service
public class BillService {
    private final JPAQueryFactory queryFactory;
    private final AppConfig appConfig;

    private final DynamicQueryUtil dynamicQueryUtil;
    private final BillExcelSourceService billExcelSourceService;

    public BillService(JPAQueryFactory queryFactory, AppConfig appConfig, DynamicQueryUtil dynamicQueryUtil, BillExcelSourceService billExcelSourceService) {
        this.queryFactory = queryFactory;
        this.appConfig = appConfig;
        this.dynamicQueryUtil = dynamicQueryUtil;
        this.billExcelSourceService = billExcelSourceService;
    }


    public JSONObject listSheetName() {
        JSONObject res = new JSONObject();
        List<String> data = queryFactory.select(qSqlTemplate.name).from(qSqlTemplate).where(qSqlTemplate.type.eq("bill")).fetch();
        res.put("data", data);
        res.put("status", true);
        return res;
    }

    @Transactional
    public JSONObject pageBySheet(PageParam param) {
        createTempTable(param.recordId, param.personId, param.type);
        String sql = queryFactory.select(qSqlTemplate.sqlTemplate).from(qSqlTemplate).where(qSqlTemplate.name.eq(param.type).and(qSqlTemplate.type.eq("bill"))).fetchFirst();
        sql = sql.replace("bill_excel_source_temp", "bill_excel_source_temp_" + param.type);
        sql = DynamicQueryUtil.replacePlaceholders(sql, param.params);
        List<Map<String, Object>> list = dynamicQueryUtil.query(sql);
        dropTempTable(param.type);

        List<Map<String, Object>> data = list.stream().map(map -> {
            map.remove("exclude1");
            map.remove("exclude2");
            map.remove("exclude3");
            return map;
        }).collect(Collectors.toList());
        if (param.num != null && param.size != null) {
            data = data.stream().skip((long) (param.num - 1) * param.size).limit(param.size).collect(Collectors.toList());
        }
        JSONObject res = new JSONObject();
        res.put("data", data);
        res.put("total", list.size());
        res.put("status", true);
        res.put("name", param.type);
        return res;
    }


    @Async("taskExecutor")
    public void generateAddress() {
        List<Tuple> billExcelSourceList = queryFactory.select(qBillExcelSource, qLacCiAddressMapping)
                .from(qBillExcelSource)
                .rightJoin(qLacCiAddressMapping)
                .on(qLacCiAddressMapping.lac.eq(qBillExcelSource.lac)
                        .and(qLacCiAddressMapping.ci.eq(qLacCiAddressMapping.ci)))
                .where(qBillExcelSource.sendAddress.isEmpty())
                .fetch();

        List<BillExcelSource> updateList = new ArrayList<>();
        for (Tuple tuple : billExcelSourceList) {
            BillExcelSource billExcelSource = tuple.get(qBillExcelSource);
            LacCiAddressMapping lacCiAddressMapping = tuple.get(qLacCiAddressMapping);

            // 检查 LacCiAddressMapping 是否为 null，避免 NullPointerException
            if (lacCiAddressMapping != null && billExcelSource != null) {
                // 更新 BillExcelSource 的 address 属性
                billExcelSource.setSendAddress(lacCiAddressMapping.getAddress());

                updateList.add(billExcelSource);
                // entityManager.merge(billExcelSource); // 假设您有一个可用的 EntityManager 实例
            }
        }
        billExcelSourceService.saveAllPartition(updateList, 1000);
    }

//    public void processBankDataApi(List<Map> data, List<Map<String, Object>> mappingList) {
//        ExcelSourceRecord billExcelSourceRecordRecord = new ExcelSourceRecord();
//        Integer recordId = 0;
//        try {
//            String suffix = "xlsx";
//            String name = System.currentTimeMillis() + "-" + "data-source-remote";
//            String fileName = name + "." + suffix;
//            String savePath = appConfig.getAnalyzeAndStatisticsDir() + fileName;
//
//            ExcelWriter writer = new ExcelWriter(savePath);
//            writer.write(data, true);
//            writer.close();
//
//            billExcelSourceRecordRecord.setName(name);
//            billExcelSourceRecordRecord.setSaveName(fileName);
//            billExcelSourceRecordRecord.setStatus("已完成");
//            billExcelSourceRecordRecord.insert();
//
//            recordId = dslContext.select(EXCEL_SOURCE_RECORD.ID).from(EXCEL_SOURCE_RECORD).orderBy(EXCEL_SOURCE_RECORD.ID.desc()).limit(1).fetchAnyInto(Long.class);
//            billExcelSourceRecordRecord.setId(recordId);
//
//            processBillData(data, mappingList, billExcelSourceRecordRecord);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

//    public void processBillData(List<Map> data, List<Map<String, Object>> mappingList, ExcelSourceRecord billExcelSourceRecordRecord) {
//        try {
//            Long finalRecordId = billExcelSourceRecordRecord.getId();
//
//            String sendName = "";
//            String sendPhone = "";
//            Map map = data.get(0);
//            if (map.containsKey("sendName")) {
//                sendName = String.valueOf(map.get("sendName"));
//            }
//            if (map.containsKey("sendPhone")) {
//                sendPhone = String.valueOf(map.get("sendPhone"));
//            }
//
//            String finalSendName = sendName;
//            String finalSendPhone = sendPhone;
//            List<BillExcelSourceRecord> insertList = data.stream().skip(1).map(item -> {
//                BillExcelSourceRecord b = dslContext.newRecord(BILL_EXCEL_SOURCE);
//
//                Map<String, Object> m = MapTransferUtil.transfer(item, mappingList);
////
//                b.fromMap(m);
//                b.setRecordId(finalRecordId);
//                if (!finalSendName.isEmpty()) {
//                    b.setSendName(finalSendName);
//                }
//                if (!finalSendPhone.isEmpty()) {
//                    b.setSendPhone(finalSendPhone);
//                }
//                return b;
//            }).collect(Collectors.toList());
//
//            dslContext.batchInsert(insertList).execute();
//
//            // 提取人员信息
//            String sql = "select send_name as name,send_phone as phone from bill_excel_source where record_id=#recordId group by send_name,send_phone\n";
//
////                   + "union all\n" +
////                    "select receive_name as name,receive_phone as phone from bill_excel_source where record_id=#recordId and length(receive_name) > 0 group by receive_name,receive_phone\n" +
////                    "union all\n" +
////                    "select concat(send_name,'-未知通话对象') as name,receive_phone as phone from bill_excel_source  where record_id=#recordId and length(receive_name) = 0 group by send_name, receive_phone";
//            sql = sql.replace("#recordId", String.valueOf(finalRecordId));
//            List<Map<String, Object>> personList = dslContext.fetch(sql).intoMaps();
//            personList.forEach(person -> {
//                String name = String.valueOf(person.get("name"));
//                dslContext.execute("INSERT INTO excel_person (name, id_card)\n" +
//                        "values ('" + name + "', '')\n" +
//                        "ON DUPLICATE KEY UPDATE id=id;" +
//                        "update excel_person set is_respondents=1 where name='" + name + "'");
//                StatisticsPersonRecord personRecord = dslContext.selectFrom(STATISTICS_PERSON).where(STATISTICS_PERSON.NAME.eq(name)).fetchAny();
//                Integer personId = personRecord.getId();
//                dslContext.execute("INSERT INTO excel_person_info (type,number,person_id,record_id)\n" +
//                        "values ('手机','" + person.get("phone") + "', '" + personId + "','" + finalRecordId + "')\n" +
//                        "ON DUPLICATE KEY UPDATE id=id;");
//            });
//
//            // 通过工具类创建writer
//            AtomicReference<ExcelWriter> writer = new AtomicReference<>(null);
//            sqlMap.forEach((key, value) -> {
//                if (writer.get() == null) {
//                    writer.set(new ExcelWriter(appConfig.getAnalyzeAndStatisticsDir() + "result-" + billExcelSourceRecordRecord.getSaveName(), key));
//                } else {
//                    writer.get().setSheet(key);
//                }
//                createTempTable(finalRecordId, null, key);
//                Result<Record> sheet1Result = dslContext.fetch(value.replace("bill_excel_source_temp", "bill_excel_source_temp_" + key));
//                dropTempTable(key);
//                List<Map<String, Object>> sheet1List = sheet1Result.stream().map(item -> {
//                    Map<String, Object> itemMap = item.intoMap();
//                    itemMap.remove("exclude1");
//                    itemMap.remove("exclude2");
//                    itemMap.remove("exclude3");
////                itemMap.entrySet().stream().filter(entry -> entry.getKey().contains("exclude")).forEach(entry -> itemMap.remove(entry.getKey()));
//                    return itemMap;
//                }).collect(Collectors.toList());
//                // 一次性写出内容，使用默认样式，强制输出标题
//                writer.get().write(sheet1List, true);
//            });
//
//            billExcelSourceRecordRecord.setStatus("已完成");
//            // 关闭writer，释放内存
//            writer.get().close();
//
//
////            LOGGER.info("文件上传成功!");
////            return fileName;
//        } catch (Exception e) {
//            billExcelSourceRecordRecord.setStatus(e.getMessage());
//            e.printStackTrace();
////            LOGGER.info("上传发生错误: {}！", e.getMessage());
////            return null;
//        } finally {
//            billExcelSourceRecordRecord.update();
//        }
//
//    }

//    public File getFile(Integer id, String type) {
//        String fileName = queryFactory.select(qExc)
//                dslContext.select(EXCEL_SOURCE_RECORD.SAVE_NAME).from(EXCEL_SOURCE_RECORD).where(EXCEL_SOURCE_RECORD.ID.eq(id)).fetchAnyInto(String.class);
//        if (type.equals("result")) {
//            fileName = "result-" + fileName;
//        }
//        fileName = appConfig.getAnalyzeAndStatisticsDir() + fileName;
//        return new File(fileName);
//    }


    private void createTempTableStructure(String tableName) {
        String sql = "CREATE TABLE " + tableName + " LIKE bill_excel_source;";
        dynamicQueryUtil.execute(sql);
    }
    private void createTempTable(Integer recordId, Integer personId, String type) {
        String tableName = "bill_excel_source_temp_" + type;
        createTempTableStructure(tableName); // 先创建临时表结构

        String insertSql = "";
        if (personId == null) {
            insertSql = "INSERT INTO " + tableName + " SELECT * FROM bill_excel_source WHERE record_id=" + recordId + ";";
        } else if (recordId == null) {
            insertSql = "INSERT INTO " + tableName + " SELECT DISTINCT b.*\n" +
                    "FROM excel_person_info pi\n" +
                    "LEFT JOIN excel_person p ON p.id = pi.person_id\n" +
                    "RIGHT JOIN bill_excel_source b ON (pi.number = b.send_phone AND p.name = b.send_name) OR (pi.number = b.receive_phone AND p.name = b.receive_name)\n" +
                    "WHERE p.id = " + personId + ";";
        } else {
            insertSql = "INSERT INTO " + tableName + " SELECT DISTINCT b.*\n" +
                    "FROM excel_person_info pi\n" +
                    "LEFT JOIN excel_person p ON p.id = pi.person_id\n" +
                    "RIGHT JOIN bill_excel_source b ON (pi.number = b.send_phone AND p.name = b.send_name) OR (pi.number = b.receive_phone AND p.name = b.receive_name)\n" +
                    "WHERE b.record_id = " + recordId + " AND p.id = " + personId + ";";
        }

        dynamicQueryUtil.execute(insertSql); // 根据条件插入数据
    }
//    private void createTempTable(Integer recordId, Integer personId, String type) {
//        dropTempTable(type);
//        String sql = "";
//        String tableName = "bill_excel_source_temp_" + type;
//        if (personId == null) {
//            sql = "CREATE TABLE  " + tableName + " SELECT * FROM bill_excel_source where record_id=" + recordId + ";";
//        } else if (recordId == null) {
//            sql = "CREATE TABLE  " + tableName + " select  distinct p.id as 'ignore', b.*\n" +
//                    "from excel_person_info pi\n" +
//                    "         left join excel_person p on p.id = pi.person_id\n" +
//                    "         right join bill_excel_source b on (pi.number = b.send_phone and p.name = b.send_name) or  (pi.number = b.receive_phone and p.name = b.receive_name)" +
//                    " where p.id = " + personId +
//                    ";";
//        } else {
//            sql = "CREATE TABLE  " + tableName + " select  distinct p.id as 'ignore', b.*\n" +
//                    "from excel_person_info pi\n" +
//                    "         left join excel_person p on p.id = pi.id\n" +
//                    "         right join bill_excel_source b on (pi.number = b.send_phone and p.name = b.send_name) or\n" +
//                    "                                          (pi.number = b.receive_phone and p.name = b.receive_name)\n" +
//                    "where b.record_id = " + recordId + " and p.id = " + personId +
//                    ";";
//        }
//        dynamicQueryUtil.execute(sql);
//    }

    private void dropTempTable(String type) {
        dynamicQueryUtil.execute("drop table if exists bill_excel_source_temp_" + type);
    }

    @Data
    public static class PageParam {
        private Integer size;
        private Integer num;
        private String contains;
        private Map<String, String> params = new HashMap<>();
        private String type;
        private Integer recordId;
        private Integer personId;
    }

    @Data
    public static class TextLineMode {
        private String text;
        private double x;
        private double y;

        @Override
        public String toString() {
            return "x=" + x +
                    ", y=" + y +
                    ", text=" + text;

        }

        public TextLineMode(String text, double x, double y) {
            this.text = text;
            this.x = x;
            this.y = y;
        }
    }

}
